package com.revenco.bi.dao.nutz;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.nutz.dao.Cnd;
import org.nutz.dao.Condition;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.stereotype.Repository;

import com.revenco.bi.dao.ChartSqlDao;
import com.revenco.bi.model.KpiPeriod;
import com.revenco.bi.model.TnTableInfo;
import com.revenco.bi.model.userbean.AnychartDataBean;

/**
 * 
 * @author zhaozz
 * 
 */
@Repository
public class ChartSqlDaoImpl extends AstractDaoImpl implements ChartSqlDao {
	private List<String> legend;
	public List<KpiPeriod> getAll() {
		return this.getDao().query(KpiPeriod.class, null);
	}

	public String sqlTest(String sql) {
		String result = "";
		try {
			Sql sqlTestObj = Sqls.create(sql);
			this.getDao().execute(sqlTestObj);
			result = "true";
		} catch (Exception e) {
			result = e.getMessage();
		}

		return result;

	}

	@SuppressWarnings("unchecked")
	public Map<String,AnychartDataBean[]> getSeriesDataTwo(String sql,
			List<String> legendList) {
		Sql sqlObj = Sqls.create(sql);
		this.legend = legendList;
        this.getDao().execute(sqlObj);
		sqlObj.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					 {
				Map<String, AnychartDataBean[]> map = new HashMap<String, AnychartDataBean[]>();
				Iterator<String> it = legend.iterator();
				String key;
				System.out.println("开始执行sql");
				int  arrayIndex=0;
				try {
					rs.last();
					int count=rs.getRow();
					rs.first();
					while (it.hasNext()) {
						key = it.next();
						//map.put(key, new AnychartDataBean[rs.getFetchSize()]);
						map.put(key, new AnychartDataBean[count]);
					}
					while (rs.next()) {
						it = legend.iterator();
						while (it.hasNext()) {
							key = it.next();
							AnychartDataBean anychartData=new AnychartDataBean();
							anychartData.setName(rs.getString("name"));
							anychartData.setId(rs.getString("id"));
							anychartData.setSumValue(rs.getString(key));
							map.get(key)[arrayIndex]=anychartData;
						}
						arrayIndex=arrayIndex<count?(count-1):arrayIndex++;
					}
					for(Map.Entry<String, AnychartDataBean[]> entry:map.entrySet()){ 
						for(int i=0;i<entry.getValue().length;i++){
							System.out.println(entry.getKey()+"--->"+entry.getValue()[i].getName()+"--->"+entry.getValue()[i].getSumValue()); 
						}
				} 
				} catch (SQLException e) {
					System.out.println("异常："+e);
					e.printStackTrace();
				}
				return map;
			}
		});
		this.getDao().execute(sqlObj);
		return sqlObj.getObject(Map.class);
	}
	@SuppressWarnings("unchecked")
	public Map<String,List<AnychartDataBean>> getSeriesData(String sql,
			List<String> legendList) {
		Sql sqlObj = Sqls.create(sql);
		this.legend = legendList;

		sqlObj.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					 {
				Map<String, List<AnychartDataBean>> map = new HashMap<String, List<AnychartDataBean>>();
				Iterator<String> it = legend.iterator();
				String key;
				System.out.println("开始执行sql");
				try {
					while (it.hasNext()) {
						key = it.next();
						map.put(key, new ArrayList<AnychartDataBean>());
					}
					while (rs.next()) {
						it = legend.iterator();
						while (it.hasNext()) {
							key = it.next();
							AnychartDataBean anychartData=new AnychartDataBean();
							anychartData.setName(rs.getString("name"));
							anychartData.setId(rs.getString("id"));
							anychartData.setSumValue(rs.getString(key));
							map.get(key).add(anychartData);
						}
					}
					for(Entry<String, List<AnychartDataBean>> entry:map.entrySet()){ 
						for(int i=0;i<entry.getValue().size();i++){
							System.out.println(entry.getKey()+"--->"+entry.getValue().get(i).getName()); 
						}
				} 
				} catch (SQLException e) {
					System.out.println("异常："+e);
					e.printStackTrace();
				}
				return map;
			}
		});
		this.getDao().execute(sqlObj);
		return sqlObj.getObject(Map.class);
	}
	
	@SuppressWarnings("unchecked")
	public Map<String, Map<String, String>> getSeriesData2(String sql,
			List<String> legendList) {
		Sql sqlObj = Sqls.create(sql);
		this.legend = legendList;

		sqlObj.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					throws SQLException {
				Map<String, Map<String, String>> map = new HashMap<String, Map<String, String>>();
				Iterator<String> it = legend.iterator();
				String key;
				while (it.hasNext()) {
					key = it.next();
					map.put(key, new HashMap<String, String>());
				}
				while (rs.next()) {
					it = legend.iterator();
					while (it.hasNext()) {
						key = it.next();
						map.get(key).put(key,
								rs.getString(key));
					}
				}
				return map;
			}
		});
		this.getDao().execute(sqlObj);
		return sqlObj.getObject(Map.class);
	}

	public TnTableInfo getTableById(String id) {
		// TODO Auto-generated method stub
		Condition condition = Cnd.where("tableId", "=", id);
		return this.getDao().query(TnTableInfo.class, condition).get(0);
	}

	public String getAsNameByName(String name) {
		try {
			Condition condition = Cnd.where("name", "=", name);
			return this.getDao().query(TnTableInfo.class, condition).get(0).getAsName();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
}
